﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Sql;
using System.Data.OleDb;
using Microsoft.SqlServer.Server;
using System.Reflection;
using System.Globalization;
using System.ComponentModel;
using System.Collections.Generic;

public partial class FileImport
{
    private const String COLUMN_NAME = "ColumnName";
    private const String COLUMN_SIZE = "ColumnSize";
    private const String DATA_TYPE = "DataType";

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ImportFromFile( SqlString tableName, 
        SqlString fileName, 
        SqlString templateFile,
        SqlString seperator,
        SqlInt16 skipLines)
    {
        SqlConnection conn = new SqlConnection(@"context connection=true");
        //SqlConnection conn = new SqlConnection(@"Data Source=SERVER4;Initial Catalog=InOrder_Live;Integrated Security=True;MultipleActiveResultSets=True; Asynchronous Processing=true");
        try
        {
            conn.Open();
        }
        catch (Exception e)
        {
            throw new Exception("Unable to open database\n" + e.Message);
        }

        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmm = new SqlCommand();
        cmm.CommandText = "select * from " + tableName.ToString();
        cmm.Connection = conn;
        da.SelectCommand = cmm;
        SqlCommandBuilder cb = new SqlCommandBuilder(da);
        try
        {
            da.FillSchema(ds, SchemaType.Source);
        }
        catch (Exception err)
        {
            throw new Exception("Unable to load table schema", err);
        }

        DataImport di = new DataImport();
        try
        {
            di.LoadData(fileName.ToString(), 
                ds.Tables[0], 
                (templateFile.IsNull) ? null : templateFile.ToString(),
                (seperator.IsNull) ? ',' : seperator.ToString()[0],
                (skipLines.IsNull) ? 0 : skipLines.Value);
        }
        catch (Exception err)
        {
            throw new Exception("Unable to load the file into the datatable", err);
        }

        try
        {
            da.Update(ds);
        }
        catch (Exception err)
        {
            throw new Exception("Unable to write data to the table", err);
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ExecQuery(SqlString connectionString, SqlString query)
    {
        string cs = connectionString.Value;
        if (!cs.Contains(";"))
            using(SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();

                SqlCommand cmm = connection.CreateCommand();
                cmm.CommandText = "select 'Provider=' + providername + '; data source=' + datasource + ';Collating Sequence=general;OLE DB Services=-4' from master..sysservers where srvname=@srvName";
                cmm.Parameters.Add(new SqlParameter("@srvName", connectionString.Value));
                cs = (string)cmm.ExecuteScalar();
            }

        // Connect through the context connection.
        using(OleDbConnection connection = new OleDbConnection(cs))
        {
            connection.Open();

            OleDbCommand command = connection.CreateCommand();
            command.CommandText = query.Value;
            OleDbDataReader rdr = command.ExecuteReader();

            Int64 columnSize = 0;
            List<SqlMetaData> sqlMetaDataList = new List<SqlMetaData>();
            foreach (DataRow dataRow in rdr.GetSchemaTable().Rows)
            {
                if (Int64.TryParse(((Int32)dataRow[COLUMN_SIZE]).ToString(CultureInfo.CurrentCulture), out columnSize))
                {
                    sqlMetaDataList.Add(ParseSqlMetaData((String)dataRow[COLUMN_NAME], (Type)dataRow[DATA_TYPE], columnSize));
                }
                else
                {
                    sqlMetaDataList.Add(ParseSqlMetaData((String)dataRow[COLUMN_NAME], (Type)dataRow[DATA_TYPE], -1));
                }
            }

            SqlDataRecord record = new SqlDataRecord(sqlMetaDataList.ToArray());
            SqlContext.Pipe.SendResultsStart(record);

            object[] objects = new object[sqlMetaDataList.Count];
            while (rdr.Read())
            {
                rdr.GetValues(objects);
                record.SetValues(objects);
                SqlContext.Pipe.SendResultsRow(record);
            }
            SqlContext.Pipe.SendResultsEnd();
        }
    }

    private static SqlMetaData ParseSqlMetaData(String columnName, Type type, Int64 maxLength)
    {
        SqlParameter sqlParameter = new SqlParameter();
        sqlParameter.DbType = (DbType)TypeDescriptor.GetConverter(sqlParameter.DbType).ConvertFrom(type.Name);
        if (sqlParameter.SqlDbType == SqlDbType.Char || sqlParameter.SqlDbType == SqlDbType.NChar || sqlParameter.SqlDbType == SqlDbType.NVarChar || sqlParameter.SqlDbType == SqlDbType.VarChar)
        {
            if (maxLength > 8000)
            {
                maxLength = -1;
            }
            return new SqlMetaData(columnName, sqlParameter.SqlDbType, maxLength);
        }
        else if (sqlParameter.SqlDbType == SqlDbType.Text || sqlParameter.SqlDbType == SqlDbType.NText)
        {
            return new SqlMetaData(columnName, sqlParameter.SqlDbType, -1);
        }
        else
        {
            return new SqlMetaData(columnName, sqlParameter.SqlDbType);
        }
    }
}

